package org.cinvestav.database;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.cinvestav.Articulo;
import org.cinvestav.Investigador;

/**
 *
 * @author Osvaldo Salinas Martínez. @contact osalinas@tamps.cinvestav.mx
 * @create 12/01/2012 12:15:05 PM
 * @version 1.0
 */
public class ConectorMySQL {

    Connection cxn = null;

    public ConectorMySQL() {
    }

    public boolean setConection() {
        for (int i = 0; i < 3; i++) {
            try {
                if (cxn == null) {
                    MysqlDataSource dataSource = new MysqlDataSource();
                    dataSource.setUser("root");
                    dataSource.setPassword("");
                    dataSource.setDatabaseName("perfiles");
                    dataSource.setServerName("localhost");
                    dataSource.setPort(3306);
                    //dataSource.setPort(3310);
                    this.cxn = (Connection) dataSource.getConnection();
                } else if (!this.cxn.isClosed()) {
                    return true;
                } else {
                    this.cxn = null;
                }
            } catch (Exception e) {
                this.cxn = null;
                System.err.println("Error (" + i + ") de conexion: " + e);
            }
        }
        return false;
    }

    public boolean isConected() {

        try {
            if (cxn.isClosed()) {
                cxn = null;
                return setConection();
            }
        } catch (Exception ex) {
            cxn = null;
            return setConection();
        }
        return true;
    }

    /**
     * Inserta un registro de investigador en la base de datos si no exist
     */
    public boolean insertInvestigador(Investigador investigador) {
        try {
            if (isConected()) {
                Statement stm = (Statement) this.cxn.createStatement();
                String query = "INSERT INTO investigador VALUES('"
                        + investigador.getCVU() + "','"
                        + investigador.getRFC() + "','"
                        + investigador.getCURP() + "','"
                        + investigador.getNumEmpleado() + "','"
                        + investigador.getNombre() + "','"
                        + investigador.getFecha_nacimiento() + "','"
                        + investigador.getLugar_nacimiento() + "','"
                        + investigador.getCategoria() + "','"
                        + investigador.getUnidad() + "','"
                        + investigador.getDepartamento() + "','"
                        + investigador.getPuestoLabora() + "');";

                if (stm.executeUpdate(query) == 1) {
                    if (insertProduccion(investigador.getProduccion())) {
                        return true;
                    }
                }
            }
        } catch (Exception e) {
            System.err.println("insertInvestigador:" + e);
        }
        return false;
    }

    public boolean insertProduccion(List<Articulo> productos) {
        try {
            if (isConected()) {
                Statement stm = (Statement) this.cxn.createStatement();
                Iterator itr = productos.iterator();
                while (itr.hasNext()) {
                    Articulo articulo = (Articulo) itr.next();
                    System.out.println("" + articulo.getTipo());
                    String query = "INSERT INTO produccion VALUES('"
                            + articulo.getTipo() + "','"
                            + articulo.getStringAutores() + "','"
                            + articulo.getTitulo() + "','"
                            + articulo.getRevista() + "','"
                            + articulo.getVolumen() + "','"
                            + articulo.getNumero() + "','"
                            + articulo.getPagina_down() + "-" + articulo.getPagina_up() + "','"
                            + articulo.getIssn() + "','"
                            + articulo.getEditorial() + "');";

                    if (stm.executeUpdate(query) == 1) {
                    }
                }
            }
        } catch (Exception e) {
            System.err.println("Error: " + e);
        }
        return false;
    }

    public Map<String, String> getTipoProduccion() {
        try {

            if (isConected()) {
                Statement stm = (Statement) this.cxn.createStatement();

                String query = "SELECT * FROM `productos`;";

                ResultSet rs = stm.executeQuery(query);

                Map<String, String> produccion = new HashMap<String, String>();

                while (rs.next()) {
                    produccion.put(rs.getString("tipo"), rs.getString("descripcion"));
                }

                return produccion;
            }

        } catch (Exception e) {
            System.out.println("" + e);
        }
        return null;
    }

    public void callProcedure(String office) {
        //Connection con = getConnection();

        if (isConected()) {

            CallableStatement cs = null;
            try {
                cs = cxn.prepareCall("{call WhoAreThey(?,?)}");
                cs.registerOutParameter(1, Types.VARCHAR);
                cs.setString(2, office);
                cs.execute();
                String str = cs.getString(1);
                if (str != null) {
                    System.out.println(str);
                } else {
                    ResultSet rs = cs.getResultSet();
                    while (rs.next()) {
                        System.out.println("Name : " + rs.getString(2));
                    }
                }
            } catch (SQLException e) {
                System.err.println("SQLException: " + e.getMessage());
            } finally {
                if (cs != null) {
                    try {
                        cs.close();
                    } catch (SQLException e) {
                        System.err.println("SQLException: " + e.getMessage());
                    }
                }
                if (cxn != null) {
                    try {
                        cxn.close();
                    } catch (SQLException e) {
                        System.err.println("SQLException: " + e.getMessage());
                    }
                }
            }

        }
    }

    public boolean close() {
        try {
            cxn.close();
            return true;
        } catch (Exception e) {
            System.out.println("Exception: " + e);
        }
        return false;
    }
}
